<!DOCTYPE html>
<html lang=zh>
<head>
    <meta charset="utf-8">
    
    <title>第七章：使用QueryDSL与SpringDataJPA实现子查询 | 恒宇少年De成长之路</title>
    <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1" />
    <meta name="description" content="在上一章我们讲到了QueryDSL的聚合函数，让我们重新认识了QueryDSL的便利之处，它可以很好的使用原生SQL的思想来进行Java形式的描述，编写完成也不需要考虑更换数据库存在的不兼容问题。当然QueryDSL还有很多我们没有发掘出来的核心技术，我们今天来讲解下”子查询“，看看QueryDSL是怎么完美的诠释了使用Java写SQL。">
<meta name="keywords" content="SpringBoot,QueryDSL">
<meta property="og:type" content="article">
<meta property="og:title" content="第七章：使用QueryDSL与SpringDataJPA实现子查询">
<meta property="og:url" content="http://blog.yuqiyu.com/querydsl-chapter-7/index.html">
<meta property="og:site_name" content="恒宇少年De成长之路">
<meta property="og:description" content="在上一章我们讲到了QueryDSL的聚合函数，让我们重新认识了QueryDSL的便利之处，它可以很好的使用原生SQL的思想来进行Java形式的描述，编写完成也不需要考虑更换数据库存在的不兼容问题。当然QueryDSL还有很多我们没有发掘出来的核心技术，我们今天来讲解下”子查询“，看看QueryDSL是怎么完美的诠释了使用Java写SQL。">
<meta property="og:locale" content="zh-CN">
<meta property="og:image" content="http://upload-images.jianshu.io/upload_images/4461954-f1d4cb64234b160a.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240">
<meta property="og:updated_time" content="2018-01-23T16:15:47.107Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="第七章：使用QueryDSL与SpringDataJPA实现子查询">
<meta name="twitter:description" content="在上一章我们讲到了QueryDSL的聚合函数，让我们重新认识了QueryDSL的便利之处，它可以很好的使用原生SQL的思想来进行Java形式的描述，编写完成也不需要考虑更换数据库存在的不兼容问题。当然QueryDSL还有很多我们没有发掘出来的核心技术，我们今天来讲解下”子查询“，看看QueryDSL是怎么完美的诠释了使用Java写SQL。">
<meta name="twitter:image" content="http://upload-images.jianshu.io/upload_images/4461954-f1d4cb64234b160a.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240">
    

    
        <link rel="alternate" href="/" title="恒宇少年De成长之路" type="application/atom+xml" />
    

    

    <link rel="stylesheet" href="/libs/font-awesome/css/font-awesome.min.css">
    <link rel="stylesheet" href="/libs/open-sans/styles.css">
    <link rel="stylesheet" href="/libs/source-code-pro/styles.css">

    <link rel="stylesheet" href="/css/style.css">

    <script src="/libs/jquery/2.1.3/jquery.min.js"></script>
    
    
        <link rel="stylesheet" href="/libs/lightgallery/css/lightgallery.min.css">
    
    
        <link rel="stylesheet" href="/libs/justified-gallery/justifiedGallery.min.css">
    
    
    
    


</head>

<body>
    <div id="container">
        <header id="header">
    <div id="header-main" class="header-inner">
        <div class="outer">
            <a href="/" id="logo">
                <i class="logo"></i>
                <span class="site-title">恒宇少年De成长之路</span>
            </a>
            <nav id="main-nav">
                
                    <a class="main-nav-link" href="/.">首页</a>
                
                    <a class="main-nav-link" href="/archives">时间轴</a>
                
                    <a class="main-nav-link" href="/categories">文章专题</a>
                
                    <a class="main-nav-link" href="/tags">标签云</a>
                
                    <a class="main-nav-link" href="/about">关于我</a>
                
            </nav>
            
                
                <nav id="sub-nav">
                    <div class="profile" id="profile-nav">
                        <a id="profile-anchor" href="javascript:;">
                            <img class="avatar" src="/css/images/avatar.png" />
                            <i class="fa fa-caret-down"></i>
                        </a>
                    </div>
                </nav>
            
            <div id="search-form-wrap">

    <form class="search-form">
        <input type="text" class="ins-search-input search-form-input" placeholder="搜索" />
        <!--button type="submit" class="search-form-submit"></button-->
    </form>
    <div class="ins-search">
    <div class="ins-search-mask"></div>
    <div class="ins-search-container">
        <div class="ins-input-wrapper">
            <input type="text" class="ins-search-input" placeholder="想要查找什么..." />
            <span class="ins-close ins-selectable"><i class="fa fa-times-circle"></i></span>
        </div>
        <div class="ins-section-wrapper">
            <div class="ins-section-container"></div>
        </div>
    </div>
</div>
<script>
(function (window) {
    var INSIGHT_CONFIG = {
        TRANSLATION: {
            POSTS: '文章',
            PAGES: '页面',
            CATEGORIES: '分类',
            TAGS: '标签',
            UNTITLED: '(未命名)',
        },
        ROOT_URL: '/',
        CONTENT_URL: '/content.json',
    };
    window.INSIGHT_CONFIG = INSIGHT_CONFIG;
})(window);
</script>
<script src="/js/insight.js"></script>

</div>
        </div>
    </div>
    <div id="main-nav-mobile" class="header-sub header-inner">
        <table class="menu outer">
            <tr>
                
                    <td ><a  class="main-nav-link" href="/.">首页</a></td>
                
                    <td ><a  class="main-nav-link" href="/archives">时间轴</a></td>
                
                    <td ><a  class="main-nav-link" href="/categories">文章专题</a></td>
                
                    <td ><a  class="main-nav-link" href="/tags">标签云</a></td>
                
                    <td ><a  class="main-nav-link" href="/about">关于我</a></td>
                
                <td>
                    
    <div class="search-form">
        <input type="text" class="ins-search-input search-form-input" placeholder="搜索" />
    </div>

                </td>
            </tr>
        </table>
    </div>
</header>

        <div class="outer">
            
                

<aside id="profile">
    <div class="inner profile-inner">
        <div class="base-info profile-block">
            <img id="avatar" src="/css/images/avatar.png" />
            <h2 id="name">恒宇少年</h2>
            <h3 id="title">Java软件工程师 &amp; 程序猿</h3>
            <span id="location"><i class="fa fa-map-marker"></i>山东, 济南</span>
            <a id="follow" target="_blank" href="https://www.jianshu.com/u/092df3f77bca">关注我</a>
        </div>
        <div class="article-info profile-block">
            <div class="article-info-block">
                43
                <span>文章</span>
            </div>
            <div class="article-info-block">
                2
                <span>标签</span>
            </div>
        </div>
        
        <div class="profile-block social-links">
            <table>
                <tr>
                    
                    
                    <td>
                        <a href="http://github.com/ppoffice/hexo-theme-icarus" target="_blank" title="github" class=tooltip>
                            <i class="fa fa-github"></i>
                        </a>
                    </td>
                    
                    <td>
                        <a href="/" target="_blank" title="twitter" class=tooltip>
                            <i class="fa fa-twitter"></i>
                        </a>
                    </td>
                    
                    <td>
                        <a href="/" target="_blank" title="facebook" class=tooltip>
                            <i class="fa fa-facebook"></i>
                        </a>
                    </td>
                    
                    <td>
                        <a href="/" target="_blank" title="dribbble" class=tooltip>
                            <i class="fa fa-dribbble"></i>
                        </a>
                    </td>
                    
                    <td>
                        <a href="/" target="_blank" title="rss" class=tooltip>
                            <i class="fa fa-rss"></i>
                        </a>
                    </td>
                    
                </tr>
            </table>
        </div>
        
    </div>
</aside>

            
            <section id="main"><article id="post-querydsl-chapter-7" class="article article-type-post" itemscope itemprop="blogPost">
    <div class="article-inner">
        
        
            <header class="article-header">
                
    
        <h1 class="article-title" itemprop="name">
            第七章：使用QueryDSL与SpringDataJPA实现子查询
        </h1>
    

                
                    <div class="article-meta">
                        
    <div class="article-date">
        <i class="fa fa-calendar"></i>
        <a href="/querydsl-chapter-7/">
            <time datetime="2017-07-13T16:00:00.000Z" itemprop="datePublished">2017-07-14</time>
        </a>
    </div>


                        
    <div class="article-category">
    	<i class="fa fa-folder"></i>
        <a class="article-category-link" href="/categories/QueryDSL-核心技术/">QueryDSL 核心技术</a>
    </div>

                        
    <div class="article-tag">
        <i class="fa fa-tag"></i>
        <a class="tag-link" href="/tags/QueryDSL/">QueryDSL</a>, <a class="tag-link" href="/tags/SpringBoot/">SpringBoot</a>
    </div>

                    </div>
                
            </header>
        
        
        <div class="article-entry" itemprop="articleBody">
        
            
            <p>在上一章我们讲到了QueryDSL的聚合函数，让我们重新认识了QueryDSL的便利之处，它可以很好的使用原生SQL的思想来进行Java形式的描述，编写完成也不需要考虑更换数据库存在的不兼容问题。当然QueryDSL还有很多我们没有发掘出来的核心技术，我们今天来讲解下”子查询“，看看QueryDSL是怎么完美的诠释了使用Java写SQL。<br><a id="more"></a></p>
<h1 id="本章目标"><a href="#本章目标" class="headerlink" title="本章目标"></a>本章目标</h1><p>基于SpringBoot平台完成QueryDSL整合JPA实现多表、单表子查询。</p>
<h1 id="构建项目"><a href="#构建项目" class="headerlink" title="构建项目"></a>构建项目</h1><p>我们使用idea工具创建一个SpringBoot项目，然后添加部分依赖并配置QueryDSL自动生成QueryBean插件，pom.xml代码如下所示：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br><span class="line">44</span><br><span class="line">45</span><br><span class="line">46</span><br><span class="line">47</span><br><span class="line">48</span><br><span class="line">49</span><br><span class="line">50</span><br><span class="line">51</span><br><span class="line">52</span><br><span class="line">53</span><br><span class="line">54</span><br><span class="line">55</span><br><span class="line">56</span><br><span class="line">57</span><br><span class="line">58</span><br><span class="line">59</span><br><span class="line">60</span><br><span class="line">61</span><br><span class="line">62</span><br><span class="line">63</span><br><span class="line">64</span><br><span class="line">65</span><br><span class="line">66</span><br><span class="line">67</span><br><span class="line">68</span><br><span class="line">69</span><br><span class="line">70</span><br><span class="line">71</span><br><span class="line">72</span><br><span class="line">73</span><br><span class="line">74</span><br><span class="line">75</span><br><span class="line">76</span><br><span class="line">77</span><br><span class="line">78</span><br><span class="line">79</span><br><span class="line">80</span><br><span class="line">81</span><br><span class="line">82</span><br><span class="line">83</span><br><span class="line">84</span><br><span class="line">85</span><br><span class="line">86</span><br><span class="line">87</span><br><span class="line">88</span><br><span class="line">89</span><br><span class="line">90</span><br><span class="line">91</span><br><span class="line">92</span><br><span class="line">93</span><br><span class="line">94</span><br><span class="line">95</span><br><span class="line">96</span><br><span class="line">97</span><br><span class="line">98</span><br><span class="line">99</span><br><span class="line">100</span><br><span class="line">101</span><br><span class="line">102</span><br><span class="line">103</span><br><span class="line">104</span><br><span class="line">105</span><br><span class="line">106</span><br><span class="line">107</span><br><span class="line">108</span><br><span class="line">109</span><br><span class="line">110</span><br><span class="line">111</span><br><span class="line">112</span><br><span class="line">113</span><br></pre></td><td class="code"><pre><span class="line">&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot;?&gt;</span><br><span class="line">&lt;project xmlns=&quot;http://maven.apache.org/POM/4.0.0&quot; xmlns:xsi=&quot;http://www.w3.org/2001/XMLSchema-instance&quot;</span><br><span class="line">    xsi:schemaLocation=&quot;http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd&quot;&gt;</span><br><span class="line">    &lt;modelVersion&gt;4.0.0&lt;/modelVersion&gt;</span><br><span class="line"></span><br><span class="line">    &lt;groupId&gt;com.yuqiyu.querydsl.sample&lt;/groupId&gt;</span><br><span class="line">    &lt;artifactId&gt;chapter7&lt;/artifactId&gt;</span><br><span class="line">    &lt;version&gt;0.0.1-SNAPSHOT&lt;/version&gt;</span><br><span class="line">    &lt;packaging&gt;war&lt;/packaging&gt;</span><br><span class="line"></span><br><span class="line">    &lt;name&gt;chapter7&lt;/name&gt;</span><br><span class="line">    &lt;description&gt;Demo project for Spring Boot&lt;/description&gt;</span><br><span class="line"></span><br><span class="line">    &lt;parent&gt;</span><br><span class="line">        &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;</span><br><span class="line">        &lt;artifactId&gt;spring-boot-starter-parent&lt;/artifactId&gt;</span><br><span class="line">        &lt;version&gt;1.5.4.RELEASE&lt;/version&gt;</span><br><span class="line">        &lt;relativePath/&gt; &lt;!-- lookup parent from repository --&gt;</span><br><span class="line">    &lt;/parent&gt;</span><br><span class="line"></span><br><span class="line">    &lt;properties&gt;</span><br><span class="line">        &lt;project.build.sourceEncoding&gt;UTF-8&lt;/project.build.sourceEncoding&gt;</span><br><span class="line">        &lt;project.reporting.outputEncoding&gt;UTF-8&lt;/project.reporting.outputEncoding&gt;</span><br><span class="line">        &lt;java.version&gt;1.8&lt;/java.version&gt;</span><br><span class="line">    &lt;/properties&gt;</span><br><span class="line"></span><br><span class="line">    &lt;dependencies&gt;</span><br><span class="line">        &lt;dependency&gt;</span><br><span class="line">            &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;</span><br><span class="line">            &lt;artifactId&gt;spring-boot-starter-data-jpa&lt;/artifactId&gt;</span><br><span class="line">        &lt;/dependency&gt;</span><br><span class="line">        &lt;dependency&gt;</span><br><span class="line">            &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;</span><br><span class="line">            &lt;artifactId&gt;spring-boot-starter-web&lt;/artifactId&gt;</span><br><span class="line">        &lt;/dependency&gt;</span><br><span class="line"></span><br><span class="line">        &lt;dependency&gt;</span><br><span class="line">            &lt;groupId&gt;mysql&lt;/groupId&gt;</span><br><span class="line">            &lt;artifactId&gt;mysql-connector-java&lt;/artifactId&gt;</span><br><span class="line">            &lt;scope&gt;runtime&lt;/scope&gt;</span><br><span class="line">        &lt;/dependency&gt;</span><br><span class="line">        &lt;!--阿里巴巴数据库连接池，专为监控而生 --&gt;</span><br><span class="line">        &lt;dependency&gt;</span><br><span class="line">            &lt;groupId&gt;com.alibaba&lt;/groupId&gt;</span><br><span class="line">            &lt;artifactId&gt;druid&lt;/artifactId&gt;</span><br><span class="line">            &lt;version&gt;1.0.26&lt;/version&gt;</span><br><span class="line">        &lt;/dependency&gt;</span><br><span class="line">        &lt;!-- 阿里巴巴fastjson，解析json视图 --&gt;</span><br><span class="line">        &lt;dependency&gt;</span><br><span class="line">            &lt;groupId&gt;com.alibaba&lt;/groupId&gt;</span><br><span class="line">            &lt;artifactId&gt;fastjson&lt;/artifactId&gt;</span><br><span class="line">            &lt;version&gt;1.2.15&lt;/version&gt;</span><br><span class="line">        &lt;/dependency&gt;</span><br><span class="line">        &lt;dependency&gt;</span><br><span class="line">            &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;</span><br><span class="line">            &lt;artifactId&gt;spring-boot-starter-tomcat&lt;/artifactId&gt;</span><br><span class="line">            &lt;!--&lt;scope&gt;provided&lt;/scope&gt;--&gt;</span><br><span class="line">        &lt;/dependency&gt;</span><br><span class="line">        &lt;dependency&gt;</span><br><span class="line">            &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;</span><br><span class="line">            &lt;artifactId&gt;spring-boot-starter-test&lt;/artifactId&gt;</span><br><span class="line">            &lt;scope&gt;test&lt;/scope&gt;</span><br><span class="line">        &lt;/dependency&gt;</span><br><span class="line">        &lt;!--queryDSL--&gt;</span><br><span class="line">        &lt;dependency&gt;</span><br><span class="line">            &lt;groupId&gt;com.querydsl&lt;/groupId&gt;</span><br><span class="line">            &lt;artifactId&gt;querydsl-jpa&lt;/artifactId&gt;</span><br><span class="line">            &lt;version&gt;$&#123;querydsl.version&#125;&lt;/version&gt;</span><br><span class="line">        &lt;/dependency&gt;</span><br><span class="line">        &lt;dependency&gt;</span><br><span class="line">            &lt;groupId&gt;com.querydsl&lt;/groupId&gt;</span><br><span class="line">            &lt;artifactId&gt;querydsl-apt&lt;/artifactId&gt;</span><br><span class="line">            &lt;version&gt;$&#123;querydsl.version&#125;&lt;/version&gt;</span><br><span class="line">            &lt;scope&gt;provided&lt;/scope&gt;</span><br><span class="line">        &lt;/dependency&gt;</span><br><span class="line">        &lt;dependency&gt;</span><br><span class="line">            &lt;groupId&gt;org.projectlombok&lt;/groupId&gt;</span><br><span class="line">            &lt;artifactId&gt;lombok&lt;/artifactId&gt;</span><br><span class="line">            &lt;version&gt;1.16.16&lt;/version&gt;</span><br><span class="line">        &lt;/dependency&gt;</span><br><span class="line">        &lt;dependency&gt;</span><br><span class="line">            &lt;groupId&gt;javax.inject&lt;/groupId&gt;</span><br><span class="line">            &lt;artifactId&gt;javax.inject&lt;/artifactId&gt;</span><br><span class="line">            &lt;version&gt;1&lt;/version&gt;</span><br><span class="line">        &lt;/dependency&gt;</span><br><span class="line">    &lt;/dependencies&gt;</span><br><span class="line"></span><br><span class="line">    &lt;build&gt;</span><br><span class="line">        &lt;plugins&gt;</span><br><span class="line">            &lt;plugin&gt;</span><br><span class="line">                &lt;groupId&gt;org.springframework.boot&lt;/groupId&gt;</span><br><span class="line">                &lt;artifactId&gt;spring-boot-maven-plugin&lt;/artifactId&gt;</span><br><span class="line">            &lt;/plugin&gt;</span><br><span class="line">            &lt;!--添加QueryDSL插件支持--&gt;</span><br><span class="line">            &lt;plugin&gt;</span><br><span class="line">                &lt;groupId&gt;com.mysema.maven&lt;/groupId&gt;</span><br><span class="line">                &lt;artifactId&gt;apt-maven-plugin&lt;/artifactId&gt;</span><br><span class="line">                &lt;version&gt;1.1.3&lt;/version&gt;</span><br><span class="line">                &lt;executions&gt;</span><br><span class="line">                    &lt;execution&gt;</span><br><span class="line">                        &lt;goals&gt;</span><br><span class="line">                            &lt;goal&gt;process&lt;/goal&gt;</span><br><span class="line">                        &lt;/goals&gt;</span><br><span class="line">                        &lt;configuration&gt;</span><br><span class="line">                            &lt;outputDirectory&gt;target/generated-sources/java&lt;/outputDirectory&gt;</span><br><span class="line">                            &lt;processor&gt;com.querydsl.apt.jpa.JPAAnnotationProcessor&lt;/processor&gt;</span><br><span class="line">                        &lt;/configuration&gt;</span><br><span class="line">                    &lt;/execution&gt;</span><br><span class="line">                &lt;/executions&gt;</span><br><span class="line">            &lt;/plugin&gt;</span><br><span class="line">        &lt;/plugins&gt;</span><br><span class="line">    &lt;/build&gt;</span><br><span class="line">&lt;/project&gt;</span><br></pre></td></tr></table></figure></p>
<p>我们从第一章到本章pom.xml内容几乎没有变动，所以有之前章节学习的小伙伴可以直接拿过来使用。<br>下面我们需要创建两表，当然为了方便我们直接使用第四章内的表结构，</p>
<h3 id="商品信息表"><a href="#商品信息表" class="headerlink" title="商品信息表"></a>商品信息表</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br></pre></td><td class="code"><pre><span class="line">-- ----------------------------</span><br><span class="line">-- Table structure for good_infos</span><br><span class="line">-- ----------------------------</span><br><span class="line">DROP TABLE IF EXISTS `good_infos`;</span><br><span class="line">CREATE TABLE `good_infos` (</span><br><span class="line">  `tg_id` int(11) NOT NULL AUTO_INCREMENT COMMENT &apos;主键自增&apos;,</span><br><span class="line">  `tg_title` varchar(50) CHARACTER SET utf8 DEFAULT NULL COMMENT &apos;商品标题&apos;,</span><br><span class="line">  `tg_price` decimal(8,2) DEFAULT NULL COMMENT &apos;商品单价&apos;,</span><br><span class="line">  `tg_unit` varchar(20) CHARACTER SET utf8 DEFAULT NULL COMMENT &apos;单位&apos;,</span><br><span class="line">  `tg_order` varchar(255) DEFAULT NULL COMMENT &apos;排序&apos;,</span><br><span class="line">  `tg_type_id` int(11) DEFAULT NULL COMMENT &apos;类型外键编号&apos;,</span><br><span class="line">  PRIMARY KEY (`tg_id`),</span><br><span class="line">  KEY `tg_type_id` (`tg_type_id`)</span><br><span class="line">) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;</span><br></pre></td></tr></table></figure>
<h3 id="商品类型表"><a href="#商品类型表" class="headerlink" title="商品类型表"></a>商品类型表</h3><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">-- ----------------------------</span><br><span class="line">-- Table structure for good_types</span><br><span class="line">-- ----------------------------</span><br><span class="line">DROP TABLE IF EXISTS `good_types`;</span><br><span class="line">CREATE TABLE `good_types` (</span><br><span class="line">  `tgt_id` int(11) NOT NULL AUTO_INCREMENT COMMENT &apos;主键自增&apos;,</span><br><span class="line">  `tgt_name` varchar(30) CHARACTER SET utf8 DEFAULT NULL COMMENT &apos;类型名称&apos;,</span><br><span class="line">  `tgt_is_show` char(1) DEFAULT NULL COMMENT &apos;是否显示&apos;,</span><br><span class="line">  `tgt_order` int(2) DEFAULT NULL COMMENT &apos;类型排序&apos;,</span><br><span class="line">  PRIMARY KEY (`tgt_id`)</span><br><span class="line">) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;</span><br></pre></td></tr></table></figure>
<h2 id="创建实体"><a href="#创建实体" class="headerlink" title="创建实体"></a>创建实体</h2><p>我们对应上面两张表的结构创建两个实体并添加对应的SpringDataJPA注解配置，如下所示：</p>
<h4 id="商品类型实体"><a href="#商品类型实体" class="headerlink" title="商品类型实体"></a>商品类型实体</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br></pre></td><td class="code"><pre><span class="line">package com.yuqiyu.querydsl.sample.chapter7.bean;</span><br><span class="line"></span><br><span class="line">import lombok.Data;</span><br><span class="line"></span><br><span class="line">import javax.persistence.*;</span><br><span class="line">import java.io.Serializable;</span><br><span class="line"></span><br><span class="line">/**</span><br><span class="line"> * ========================</span><br><span class="line"> * Created with IntelliJ IDEA.</span><br><span class="line"> * User：恒宇少年</span><br><span class="line"> * Date：2017/7/14</span><br><span class="line"> * Time：10:04</span><br><span class="line"> * 码云：http://git.oschina.net/jnyqy</span><br><span class="line"> * ========================</span><br><span class="line"> */</span><br><span class="line">@Entity</span><br><span class="line">@Table(name = &quot;good_types&quot;)</span><br><span class="line">@Data</span><br><span class="line">public class GoodTypeBean</span><br><span class="line">    implements Serializable</span><br><span class="line">&#123;</span><br><span class="line">    //主键</span><br><span class="line">    @Id</span><br><span class="line">    @GeneratedValue</span><br><span class="line">    @Column(name = &quot;tgt_id&quot;)</span><br><span class="line">    private Long id;</span><br><span class="line">    //类型名称</span><br><span class="line">    @Column(name = &quot;tgt_name&quot;)</span><br><span class="line">    private String name;</span><br><span class="line">    //是否显示</span><br><span class="line">    @Column(name = &quot;tgt_is_show&quot;)</span><br><span class="line">    private int isShow;</span><br><span class="line">    //排序</span><br><span class="line">    @Column(name = &quot;tgt_order&quot;)</span><br><span class="line">    private int order;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h4 id="商品实体"><a href="#商品实体" class="headerlink" title="商品实体"></a>商品实体</h4><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br><span class="line">39</span><br><span class="line">40</span><br><span class="line">41</span><br><span class="line">42</span><br><span class="line">43</span><br></pre></td><td class="code"><pre><span class="line">package com.yuqiyu.querydsl.sample.chapter7.bean;</span><br><span class="line"></span><br><span class="line">import lombok.Data;</span><br><span class="line"></span><br><span class="line">import javax.persistence.*;</span><br><span class="line">import java.io.Serializable;</span><br><span class="line"></span><br><span class="line">/**</span><br><span class="line"> * ========================</span><br><span class="line"> * Created with IntelliJ IDEA.</span><br><span class="line"> * User：恒宇少年</span><br><span class="line"> * Date：2017/7/14</span><br><span class="line"> * Time：10:08</span><br><span class="line"> * 码云：http://git.oschina.net/jnyqy</span><br><span class="line"> * ========================</span><br><span class="line"> */</span><br><span class="line">@Entity</span><br><span class="line">@Table(name = &quot;good_infos&quot;)</span><br><span class="line">@Data</span><br><span class="line">public class GoodInfoBean</span><br><span class="line">    implements Serializable</span><br><span class="line">&#123;</span><br><span class="line">    //主键</span><br><span class="line">    @Id</span><br><span class="line">    @GeneratedValue</span><br><span class="line">    @Column(name = &quot;tg_id&quot;)</span><br><span class="line">    private Long id;</span><br><span class="line">    //商品标题</span><br><span class="line">    @Column(name = &quot;tg_title&quot;)</span><br><span class="line">    private String title;</span><br><span class="line">    //商品价格</span><br><span class="line">    @Column(name = &quot;tg_price&quot;)</span><br><span class="line">    private double price;</span><br><span class="line">    //商品单位</span><br><span class="line">    @Column(name = &quot;tg_unit&quot;)</span><br><span class="line">    private String unit;</span><br><span class="line">    //商品排序</span><br><span class="line">    @Column(name = &quot;tg_order&quot;)</span><br><span class="line">    private int order;</span><br><span class="line">    //类型外键</span><br><span class="line">    @Column(name = &quot;tg_type_id&quot;)</span><br><span class="line">    private Long typeId;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure>
<h2 id="创建控制器"><a href="#创建控制器" class="headerlink" title="创建控制器"></a>创建控制器</h2><p>接下来我们创建一个商品控制器用来我们本章内容的讲解，在控制器初始化时我们需要实例化JPAQueryFactory对象，在实例化之前需要注入EntityManager对象，代码如下所示：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br></pre></td><td class="code"><pre><span class="line">package com.yuqiyu.querydsl.sample.chapter7.controller;</span><br><span class="line"></span><br><span class="line">import com.querydsl.jpa.JPAExpressions;</span><br><span class="line">import com.querydsl.jpa.impl.JPAQueryFactory;</span><br><span class="line">import org.springframework.beans.factory.annotation.Autowired;</span><br><span class="line">import org.springframework.web.bind.annotation.RequestMapping;</span><br><span class="line">import org.springframework.web.bind.annotation.RestController;</span><br><span class="line"></span><br><span class="line">import javax.annotation.PostConstruct;</span><br><span class="line">import javax.persistence.EntityManager;</span><br><span class="line">import java.util.List;</span><br><span class="line"></span><br><span class="line">/**</span><br><span class="line"> * ========================</span><br><span class="line"> * Created with IntelliJ IDEA.</span><br><span class="line"> * User：恒宇少年</span><br><span class="line"> * Date：2017/7/14</span><br><span class="line"> * Time：9:30</span><br><span class="line"> * 码云：http://git.oschina.net/jnyqy</span><br><span class="line"> * ========================</span><br><span class="line"> */</span><br><span class="line">@RestController</span><br><span class="line">public class GoodController</span><br><span class="line">&#123;</span><br><span class="line">    //实体管理对象</span><br><span class="line">    @Autowired</span><br><span class="line">    private EntityManager entityManager;</span><br><span class="line"></span><br><span class="line">    //jpa查询工厂对象</span><br><span class="line">    private JPAQueryFactory queryFactory;</span><br><span class="line"></span><br><span class="line">    @PostConstruct</span><br><span class="line">    public void init()</span><br><span class="line">    &#123;</span><br><span class="line">        queryFactory = new JPAQueryFactory(entityManager);</span><br><span class="line">    &#125;</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></p>
<h3 id="模糊查询"><a href="#模糊查询" class="headerlink" title="模糊查询"></a>模糊查询</h3><p>我们现在有个需求需要查询出商品类型名称包含蔬菜的商品列表，在原生SQL内也有多种方式可以实现如：子查询、关联查询等。我们在QueryDSL内也是一样的，我们就拿子查询来处理这个需求吧，方法代码如下所示：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br></pre></td><td class="code"><pre><span class="line">/**</span><br><span class="line"> * 子查询 模糊查询</span><br><span class="line"> * @return</span><br><span class="line"> */</span><br><span class="line">@RequestMapping(value = &quot;/childLikeSelect&quot;)</span><br><span class="line">public List&lt;GoodInfoBean&gt; childLikeSelect()</span><br><span class="line">&#123;</span><br><span class="line">    //商品基本信息查询实体</span><br><span class="line">    QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;</span><br><span class="line">    //商品类型查询实体</span><br><span class="line">    QGoodTypeBean _Q_good_type = QGoodTypeBean.goodTypeBean;</span><br><span class="line"></span><br><span class="line">    return queryFactory</span><br><span class="line">            .selectFrom(_Q_good)//查询商品基本信息表</span><br><span class="line">            .where(</span><br><span class="line">                    //查询类型名称包含“蔬菜”</span><br><span class="line">                    _Q_good.typeId.in(</span><br><span class="line">                            JPAExpressions.select(</span><br><span class="line">                                    _Q_good_type.id</span><br><span class="line">                            )</span><br><span class="line">                            .from(_Q_good_type)</span><br><span class="line">                            .where(_Q_good_type.name.like(&quot;%蔬菜%&quot;))</span><br><span class="line">                    )</span><br><span class="line">            ).fetch();</span><br><span class="line">&#125;</span><br></pre></td></tr></table></figure></p>
<p>我们上面的代码查询了商品表内的全部信息并且根据类型编号使用了”in”方法来实现子查询，子查询是查询的商品类型表内的信息并且类型的名称包含“蔬菜”，不过子查询仅仅返回了商品类型的编号。</p>
<p>我们来启动下项目测试我们这个方法是否是我们预期的效果查询出商品类型名称包含”蔬菜“两个字的列表。<br>项目启动控制台输出日志出现Tomcat started on port(s): 8080 (http)，表示已经启动成功，日志如下所示：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">  .   ____          _            __ _ _</span><br><span class="line"> /\\ / ___&apos;_ __ _ _(_)_ __  __ _ \ \ \ \</span><br><span class="line">( ( )\___ | &apos;_ | &apos;_| | &apos;_ \/ _` | \ \ \ \</span><br><span class="line"> \\/  ___)| |_)| | | | | || (_| |  ) ) ) )</span><br><span class="line">  &apos;  |____| .__|_| |_|_| |_\__, | / / / /</span><br><span class="line"> =========|_|==============|___/=/_/_/_/</span><br><span class="line"> :: Spring Boot ::        (v1.5.4.RELEASE)</span><br><span class="line"></span><br><span class="line">......</span><br><span class="line">2017-07-14 10:15:22.255  INFO 11884 --- [           main] s.b.c.e.t.TomcatEmbeddedServletContainer : Tomcat started on port(s): 8080 (http)</span><br><span class="line">2017-07-14 10:15:22.259  INFO 11884 --- [           main] c.y.q.s.chapter7.Chapter7Application     : Started Chapter7Application in 2.941 seconds (JVM running for 3.578)</span><br><span class="line">2017-07-14 10:15:26.086  INFO 11884 --- [nio-8080-exec-1] o.a.c.c.C.[Tomcat].[localhost].[/]       : Initializing Spring FrameworkServlet &apos;dispatcherServlet&apos;</span><br><span class="line">2017-07-14 10:15:26.086  INFO 11884 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet &apos;dispatcherServlet&apos;: initialization started</span><br><span class="line">2017-07-14 10:15:26.104  INFO 11884 --- [nio-8080-exec-1] o.s.web.servlet.DispatcherServlet        : FrameworkServlet &apos;dispatcherServlet&apos;: initialization completed in 18 ms</span><br><span class="line">2017-07-14 10:15:26.215  INFO 11884 --- [nio-8080-exec-1] o.h.h.i.QueryTranslatorFactoryInitiator  : HHH000397: Using ASTQueryTranslatorFactory</span><br></pre></td></tr></table></figure></p>
<p>访问我们配置的地址<a href="http://127.0.0.1:8080/childLikeSelect" target="_blank" rel="noopener">http://127.0.0.1:8080/childLikeSelect</a>查看界面输出内容如下：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">[</span><br><span class="line">    &#123;</span><br><span class="line">        &quot;id&quot;: 2,</span><br><span class="line">        &quot;title&quot;: &quot;油菜&quot;,</span><br><span class="line">        &quot;price&quot;: 12.6,</span><br><span class="line">        &quot;unit&quot;: &quot;斤&quot;,</span><br><span class="line">        &quot;order&quot;: 2,</span><br><span class="line">        &quot;typeId&quot;: 1</span><br><span class="line">    &#125;</span><br><span class="line">]</span><br></pre></td></tr></table></figure></p>
<p>我们看到数据返回”油菜“对应的商品类型编号是”1”，对应数据库的类型是”绿色蔬菜“，这证明了我们的编码跟返回的数据是一致的，那么接下来我们来看下QueryDSL为我们自动生成的SQL，如下所示：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">Hibernate: </span><br><span class="line">    select</span><br><span class="line">        goodinfobe0_.tg_id as tg_id1_0_,</span><br><span class="line">        goodinfobe0_.tg_order as tg_order2_0_,</span><br><span class="line">        goodinfobe0_.tg_price as tg_price3_0_,</span><br><span class="line">        goodinfobe0_.tg_title as tg_title4_0_,</span><br><span class="line">        goodinfobe0_.tg_type_id as tg_type_5_0_,</span><br><span class="line">        goodinfobe0_.tg_unit as tg_unit6_0_ </span><br><span class="line">    from</span><br><span class="line">        good_infos goodinfobe0_ </span><br><span class="line">    where</span><br><span class="line">        goodinfobe0_.tg_type_id in (</span><br><span class="line">            select</span><br><span class="line">                goodtypebe1_.tgt_id </span><br><span class="line">            from</span><br><span class="line">                good_types goodtypebe1_ </span><br><span class="line">            where</span><br><span class="line">                goodtypebe1_.tgt_name like ? escape &apos;!&apos;</span><br><span class="line">        )</span><br></pre></td></tr></table></figure></p>
<h2 id="价格最高的商品列表"><a href="#价格最高的商品列表" class="headerlink" title="价格最高的商品列表"></a>价格最高的商品列表</h2><p>我们又有了新的需求，需要查询出价格最高的商品列表，代码如下所示：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br></pre></td><td class="code"><pre><span class="line">/**</span><br><span class="line">     * 子查询 价格最高的商品列表</span><br><span class="line">     * @return</span><br><span class="line">     */</span><br><span class="line">    @RequestMapping(value = &quot;/childEqSelect&quot;)</span><br><span class="line">    public List&lt;GoodInfoBean&gt; childEqSelect()</span><br><span class="line">    &#123;</span><br><span class="line">        //商品基本信息查询实体</span><br><span class="line">        QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;</span><br><span class="line"></span><br><span class="line">        return queryFactory</span><br><span class="line">                .selectFrom(_Q_good)</span><br><span class="line">                //查询价格最大的商品列表</span><br><span class="line">                .where(_Q_good.price.eq(</span><br><span class="line">                        JPAExpressions.select(</span><br><span class="line">                                _Q_good.price.max()</span><br><span class="line">                        )</span><br><span class="line">                        .from(_Q_good)</span><br><span class="line">                ))</span><br><span class="line">                .fetch();</span><br><span class="line">    &#125;</span><br></pre></td></tr></table></figure></p>
<p>我们使用JPAExpressions创建一个子查询，查询出商品表内最大商品价格作为父查询的查询条件。</p>
<p>重启项目后访问地址<a href="http://127.0.0.1:8080/childEqSelect" target="_blank" rel="noopener">http://127.0.0.1:8080/childEqSelect</a>，接口返回内容如下所示：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br></pre></td><td class="code"><pre><span class="line">[</span><br><span class="line">    &#123;</span><br><span class="line">        &quot;id&quot;: 4,</span><br><span class="line">        &quot;title&quot;: &quot;秋葵&quot;,</span><br><span class="line">        &quot;price&quot;: 22.6,</span><br><span class="line">        &quot;unit&quot;: &quot;斤&quot;,</span><br><span class="line">        &quot;order&quot;: 3,</span><br><span class="line">        &quot;typeId&quot;: 1</span><br><span class="line">    &#125;</span><br><span class="line">]</span><br></pre></td></tr></table></figure></p>
<p>我们数据库内有三条数据，价格最高的也就是名为“秋葵”的商品了。下面我们再来看下控制台输出的SQL如下所示：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">Hibernate: </span><br><span class="line">    select</span><br><span class="line">        goodinfobe0_.tg_id as tg_id1_0_,</span><br><span class="line">        goodinfobe0_.tg_order as tg_order2_0_,</span><br><span class="line">        goodinfobe0_.tg_price as tg_price3_0_,</span><br><span class="line">        goodinfobe0_.tg_title as tg_title4_0_,</span><br><span class="line">        goodinfobe0_.tg_type_id as tg_type_5_0_,</span><br><span class="line">        goodinfobe0_.tg_unit as tg_unit6_0_ </span><br><span class="line">    from</span><br><span class="line">        good_infos goodinfobe0_ </span><br><span class="line">    where</span><br><span class="line">        goodinfobe0_.tg_price=(</span><br><span class="line">            select</span><br><span class="line">                max(goodinfobe1_.tg_price) </span><br><span class="line">            from</span><br><span class="line">                good_infos goodinfobe1_</span><br><span class="line">        )</span><br></pre></td></tr></table></figure></p>
<h2 id="价格高于平均价格的商品列表"><a href="#价格高于平均价格的商品列表" class="headerlink" title="价格高于平均价格的商品列表"></a>价格高于平均价格的商品列表</h2><p>现在我们需要查询高于平均价格的商品列表，那我们该怎么编写呢？代码如下所示：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br></pre></td><td class="code"><pre><span class="line">/**</span><br><span class="line">    * 子查询 价格高于平均价格的商品列表</span><br><span class="line">    * @return</span><br><span class="line">    */</span><br><span class="line">   @RequestMapping(value = &quot;/childGtAvgSelect&quot;)</span><br><span class="line">   public List&lt;GoodInfoBean&gt; childGtAvgSelect()</span><br><span class="line">   &#123;</span><br><span class="line">       //商品基本信息查询实体</span><br><span class="line">       QGoodInfoBean _Q_good = QGoodInfoBean.goodInfoBean;</span><br><span class="line">       return queryFactory</span><br><span class="line">               .selectFrom(_Q_good)</span><br><span class="line">               //查询价格高于平均价的商品列表</span><br><span class="line">               .where(</span><br><span class="line">                       _Q_good.price.gt(</span><br><span class="line">                               JPAExpressions.select(_Q_good.price.avg())</span><br><span class="line">                               .from(_Q_good)</span><br><span class="line">                       )</span><br><span class="line">               ).fetch();</span><br><span class="line">   &#125;</span><br></pre></td></tr></table></figure></p>
<p>我们使用JPAExpressions来创建一个子查询并且返回商品表内价格平均值，查询到的值作为父查询的查询条件。<br>接下来我们重启项目后访问地址<a href="http://127.0.0.1:8080/childGtAvgSelect" target="_blank" rel="noopener">http://127.0.0.1:8080/childGtAvgSelect</a>，接口返回的内容如下所示：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line">[</span><br><span class="line">    &#123;</span><br><span class="line">        &quot;id&quot;: 1,</span><br><span class="line">        &quot;title&quot;: &quot;金针菇&quot;,</span><br><span class="line">        &quot;price&quot;: 5.5,</span><br><span class="line">        &quot;unit&quot;: &quot;斤&quot;,</span><br><span class="line">        &quot;order&quot;: 1,</span><br><span class="line">        &quot;typeId&quot;: 3</span><br><span class="line">    &#125;,</span><br><span class="line">    &#123;</span><br><span class="line">        &quot;id&quot;: 2,</span><br><span class="line">        &quot;title&quot;: &quot;油菜&quot;,</span><br><span class="line">        &quot;price&quot;: 12.6,</span><br><span class="line">        &quot;unit&quot;: &quot;斤&quot;,</span><br><span class="line">        &quot;order&quot;: 2,</span><br><span class="line">        &quot;typeId&quot;: 1</span><br><span class="line">    &#125;</span><br><span class="line">]</span><br></pre></td></tr></table></figure></p>
<p>我们再来看下控制台输出的生成SQL内容如下所示：<br><figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br></pre></td><td class="code"><pre><span class="line">Hibernate: </span><br><span class="line">    select</span><br><span class="line">        goodinfobe0_.tg_id as tg_id1_0_,</span><br><span class="line">        goodinfobe0_.tg_order as tg_order2_0_,</span><br><span class="line">        goodinfobe0_.tg_price as tg_price3_0_,</span><br><span class="line">        goodinfobe0_.tg_title as tg_title4_0_,</span><br><span class="line">        goodinfobe0_.tg_type_id as tg_type_5_0_,</span><br><span class="line">        goodinfobe0_.tg_unit as tg_unit6_0_ </span><br><span class="line">    from</span><br><span class="line">        good_infos goodinfobe0_ </span><br><span class="line">    where</span><br><span class="line">        goodinfobe0_.tg_price&lt;(</span><br><span class="line">            select</span><br><span class="line">                avg(goodinfobe1_.tg_price) </span><br><span class="line">            from</span><br><span class="line">                good_infos goodinfobe1_</span><br><span class="line">        )</span><br></pre></td></tr></table></figure></p>
<p>我们可以看到生成的SQL完全是按照我们预期来创建的。</p>
<h1 id="总结"><a href="#总结" class="headerlink" title="总结"></a>总结</h1><p>以上内容就是本章的全部内容，我们使用三个简单的例子来讲述了QueryDSL子查询，QueryDSL完美的将原生的SQL编写方式转移到了Java程序内，内置了几乎所有的原生SQL的函数、关键字、语法等。</p>
<p>本章代码已经上传到码云：<br>SpringBoot配套源码地址：<a href="https://gitee.com/hengboy/spring-boot-chapter" target="_blank" rel="noopener">https://gitee.com/hengboy/spring-boot-chapter</a><br>SpringCloud配套源码地址：<a href="https://gitee.com/hengboy/spring-cloud-chapter" target="_blank" rel="noopener">https://gitee.com/hengboy/spring-cloud-chapter</a><br>SpringBoot相关系列文章请访问：<a href="http://www.jianshu.com/p/9a08417e4e84" target="_blank" rel="noopener">目录：SpringBoot学习目录</a><br>QueryDSL相关系列文章请访问：<a href="http://www.jianshu.com/p/99a5ec5c3bd5" target="_blank" rel="noopener">QueryDSL通用查询框架学习目录</a><br>SpringDataJPA相关系列文章请访问：<a href="http://www.jianshu.com/p/615ed9c1fe84" target="_blank" rel="noopener">目录：SpringDataJPA学习目录</a><br>感谢阅读！<br>欢迎加入QQ技术交流群，共同进步。<br><img src="http://upload-images.jianshu.io/upload_images/4461954-f1d4cb64234b160a.png?imageMogr2/auto-orient/strip%7CimageView2/2/w/1240" alt="QQ技术交流群"></p>

        
        </div>
        <footer class="article-footer">
            <div class="share-container">


    <div class="bdsharebuttonbox">
    <a href="#" class="bds_more" data-cmd="more">分享到：</a>
    <a href="#" class="bds_qzone" data-cmd="qzone" title="分享到QQ空间">QQ空间</a>
    <a href="#" class="bds_tsina" data-cmd="tsina" title="分享到新浪微博">新浪微博</a>
    <a href="#" class="bds_tqq" data-cmd="tqq" title="分享到腾讯微博">腾讯微博</a>
    <a href="#" class="bds_renren" data-cmd="renren" title="分享到人人网">人人网</a>
    <a href="#" class="bds_weixin" data-cmd="weixin" title="分享到微信">微信</a>
</div>
<script>
window._bd_share_config={"common":{"bdSnsKey":{},"bdText":"","bdMini":"2","bdMiniList":false,"bdPic":"","bdStyle":"0","bdSize":"16"},"share":{"bdSize":16}};with(document)0[(getElementsByTagName('head')[0]||body).appendChild(createElement('script')).src='http://bdimg.share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion='+~(-new Date()/36e5)];
</script>
<style>
    .bdshare_popup_box {
        border-radius: 4px;
        border: #e1e1e1 solid 1px;
    }
    .bdshare-button-style0-16 a,
    .bdshare-button-style0-16 .bds_more {
        padding-left: 20px;
        margin: 6px 10px 6px 0;
    }
    .bdshare_dialog_list a,
    .bdshare_popup_list a,
    .bdshare_popup_bottom a {
        font-family: 'Microsoft Yahei';
    }
    .bdshare_popup_top {
        display: none;
    }
    .bdshare_popup_bottom {
        height: auto;
        padding: 5px;
    }
</style>


</div>

            
    

        </footer>
    </div>
    
        
<nav id="article-nav">
    
        <a href="/spring-boot-chapter-27/" id="article-nav-newer" class="article-nav-link-wrap">
            <strong class="article-nav-caption">上一篇</strong>
            <div class="article-nav-title">
                
                    第二十七章：SpringBoot使用ApplicationEvent &amp; Listener完成业务解耦
                
            </div>
        </a>
    
    
        <a href="/querydsl-chapter-6/" id="article-nav-older" class="article-nav-link-wrap">
            <strong class="article-nav-caption">下一篇</strong>
            <div class="article-nav-title">第六章：使用QueryDSL的聚合函数</div>
        </a>
    
</nav>


    
</article>


    
    

</section>
            
			
				
<aside id="sidebar">
   
        
    <div class="widget-wrap">
        <h3 class="widget-title">最新文章</h3>
        <div class="widget">
            <ul id="recent-post" class="no-thumbnail">
                
                    <li>
                        
                        <div class="item-inner">
                            <p class="item-category"><a class="article-category-link" href="/categories/SpringBoot-核心技术/">SpringBoot 核心技术</a></p>
                            <p class="item-title"><a href="/spring-boot-chapter-52/" class="title">第五十二章：基于SpringBoot2使用Rest访问MongoDB数据</a></p>
                            <p class="item-date"><time datetime="2018-04-21T16:00:00.000Z" itemprop="datePublished">2018-04-22</time></p>
                        </div>
                    </li>
                
                    <li>
                        
                        <div class="item-inner">
                            <p class="item-category"><a class="article-category-link" href="/categories/SpringBoot-核心技术/">SpringBoot 核心技术</a></p>
                            <p class="item-title"><a href="/spring-boot-chapter-51/" class="title">第五十一章：基于SpringBoot2 &amp; MongoDB完成自动化集成</a></p>
                            <p class="item-date"><time datetime="2018-04-15T16:00:00.000Z" itemprop="datePublished">2018-04-16</time></p>
                        </div>
                    </li>
                
                    <li>
                        
                        <div class="item-inner">
                            <p class="item-category"><a class="article-category-link" href="/categories/SpringBoot-核心技术/">SpringBoot 核心技术</a></p>
                            <p class="item-title"><a href="/spring-boot-chapter-50/" class="title">第五十章：SpringBoot2.0新特性 - 岂止至今最简单redis缓存集成</a></p>
                            <p class="item-date"><time datetime="2018-04-14T16:00:00.000Z" itemprop="datePublished">2018-04-15</time></p>
                        </div>
                    </li>
                
                    <li>
                        
                        <div class="item-inner">
                            <p class="item-category"><a class="article-category-link" href="/categories/SpringBoot-核心技术/">SpringBoot 核心技术</a></p>
                            <p class="item-title"><a href="/spring-boot-chapter-49/" class="title">第四十九章：SpringBoot2.0新特性 - 你get到WebMvcConfigurer两种配置方式了吗？</a></p>
                            <p class="item-date"><time datetime="2018-03-16T16:00:00.000Z" itemprop="datePublished">2018-03-17</time></p>
                        </div>
                    </li>
                
                    <li>
                        
                        <div class="item-inner">
                            <p class="item-category"><a class="article-category-link" href="/categories/SpringBoot-核心技术/">SpringBoot 核心技术</a></p>
                            <p class="item-title"><a href="/spring-boot-chapter-48/" class="title">第四十八章：SpringBoot2.0新特性 - RabbitMQ信任package设置</a></p>
                            <p class="item-date"><time datetime="2018-03-12T16:00:00.000Z" itemprop="datePublished">2018-03-13</time></p>
                        </div>
                    </li>
                
            </ul>
        </div>
    </div>

    
        
    <div class="widget-wrap">
        <h3 class="widget-title">分类</h3>
        <div class="widget">
            <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/categories/QueryDSL-核心技术/">QueryDSL 核心技术</a><span class="category-list-count">7</span></li><li class="category-list-item"><a class="category-list-link" href="/categories/SpringBoot-核心技术/">SpringBoot 核心技术</a><span class="category-list-count">36</span></li></ul>
        </div>
    </div>

    
        
    <div class="widget-wrap">
        <h3 class="widget-title">归档</h3>
        <div class="widget">
            <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/04/">四月 2018</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/03/">三月 2018</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2018/01/">一月 2018</a><span class="archive-list-count">1</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/12/">十二月 2017</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/11/">十一月 2017</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/10/">十月 2017</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/09/">九月 2017</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/08/">八月 2017</a><span class="archive-list-count">4</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/07/">七月 2017</a><span class="archive-list-count">9</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/06/">六月 2017</a><span class="archive-list-count">2</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/05/">五月 2017</a><span class="archive-list-count">3</span></li><li class="archive-list-item"><a class="archive-list-link" href="/archives/2017/04/">四月 2017</a><span class="archive-list-count">5</span></li></ul>
        </div>
    </div>

    
        
    <div class="widget-wrap">
        <h3 class="widget-title">标签</h3>
        <div class="widget">
            <ul class="tag-list"><li class="tag-list-item"><a class="tag-list-link" href="/tags/QueryDSL/">QueryDSL</a><span class="tag-list-count">7</span></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/SpringBoot/">SpringBoot</a><span class="tag-list-count">43</span></li></ul>
        </div>
    </div>

    
        
    <div class="widget-wrap">
        <h3 class="widget-title">标签云</h3>
        <div class="widget tagcloud">
            <a href="/tags/QueryDSL/" style="font-size: 10px;">QueryDSL</a> <a href="/tags/SpringBoot/" style="font-size: 20px;">SpringBoot</a>
        </div>
    </div>

    
        
    <div class="widget-wrap widget-list">
        <h3 class="widget-title">链接</h3>
        <div class="widget">
            <ul>
                
                    <li>
     				<a href="http://hexo.io" target="_blank">Hexo</a>
					</li>
                
            </ul>
        </div>
    </div>


    
    <div id="toTop" class="fa fa-angle-up"></div>
</aside>

				
        </div>
        <!--引入不蒜子-->
<script async src="//dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js"></script>
<footer id="footer">
    <div class="outer">
        <div id="footer-info" class="inner">
            <span  id="busuanzi_container_site_pv">本站总访问量<span id="busuanzi_value_site_pv"></span>次</span>
            &copy; 2017 - 2018 恒宇少年 - 版权所有<br>
        </div>
    </div>
</footer>

        


    
        <script src="/libs/lightgallery/js/lightgallery.min.js"></script>
        <script src="/libs/lightgallery/js/lg-thumbnail.min.js"></script>
        <script src="/libs/lightgallery/js/lg-pager.min.js"></script>
        <script src="/libs/lightgallery/js/lg-autoplay.min.js"></script>
        <script src="/libs/lightgallery/js/lg-fullscreen.min.js"></script>
        <script src="/libs/lightgallery/js/lg-zoom.min.js"></script>
        <script src="/libs/lightgallery/js/lg-hash.min.js"></script>
        <script src="/libs/lightgallery/js/lg-share.min.js"></script>
        <script src="/libs/lightgallery/js/lg-video.min.js"></script>
    
    
        <script src="/libs/justified-gallery/jquery.justifiedGallery.min.js"></script>
    
    



<!-- Custom Scripts -->
<script src="/js/main.js"></script>

    </div>
</body>
</html>